package eBrain.model;


import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import eBrain.beans.Bid;
import eBrain.beans.User;

public class UserManager {
		
		public static boolean validatePassword(DataManager dataManager, String UserId, String pw){
			Connection connection  = dataManager.getConnection();
			boolean validator = false;
			
			if (connection != null) {
		  		try {
			  		Statement s = connection.createStatement();
				  	String sql="SELECT COUNT(*) FROM MEMBER WHERE USER_NAME ='"+UserId+"' AND PASSWORD='"+pw+"'";
		        try {
		  			  ResultSet rs = s.executeQuery(sql);	
		          try {
		        	  if (rs.next()){
		        	  int rows = rs.getInt(1);
		        	  if (rows==1){
		        		  validator = true;
		        	  }
		        	  else { validator = false; 
		        	  }
		        	  }
		        	  
		            }
		          finally { rs.close(); }
		          }
		        finally { s.close(); }
				    }
		      catch (SQLException e) {
		        System.out.println("Could not get User: " + e.getMessage());
		        }
		      finally {
		        dataManager.putConnection(connection);
		        }
		      }
			
			return validator;
		}
		
		public static User getUserByUserName(DataManager dataManager, String UserName){
					
					Connection connection  = dataManager.getConnection();
					boolean validator = false;
					User user = new User();
					
					if (connection != null) {
				  		try {
					  		Statement s = connection.createStatement();
						  	String sql="SELECT * FROM MEMBER WHERE USER_NAME ='"+UserName+"'";
				        try {
				  			  ResultSet rs = s.executeQuery(sql);	
				          try {
				        	  while(rs.next()){
				        		 
				        		  user.setUser_id(rs.getInt(1));
				        		  user.setFirstname(rs.getString(2).trim());
				        		  user.setLastname(rs.getString(3).trim());
				        		  user.setUsername(rs.getString(4).trim());
				        		  user.setBirthday(rs.getDate(5));
				        		  user.setGender(rs.getString(6).trim());
				        		  user.setEmail(rs.getString(7).trim()); 
				        		  user.setPassword(rs.getString(8).trim());
				        		  user.setStreet(rs.getString(9).trim());
				        		  user.setZIP(rs.getString(10).trim());
				        		  user.setCity(rs.getString(11).trim());
				        		  user.setCountry(rs.getString(12));
				        		  
				        	  }
				        	  
				            }
				          finally { rs.close(); }
				          }
				        finally { s.close(); }
						    }
				      catch (SQLException e) {
				        System.out.println("Could not get User for this USER_ID: "+ UserName +"/n"+ e.getMessage());
				        }
				      finally {
				        dataManager.putConnection(connection);
				        }
					}    
				  	return user;
		}
		
		public static String getUsernameByUserId(DataManager dataManager, int userId){
			
			Connection connection = dataManager.getConnection();
			PreparedStatement pstmt = null;
			String username = null;
			if (connection != null) {
				try {
					Statement s = connection.createStatement();
					String sql = "SELECT USER_NAME FROM MEMBER WHERE USER_ID = ?";
					pstmt = connection.prepareStatement(sql); 
					pstmt.setInt(1, userId);
					
					try {
						ResultSet rs = pstmt.executeQuery();
						try {
							while (rs.next()) {
								username=rs.getString(1);
							}
						} finally {
							rs.close();
						}
					} finally {
						s.close();
						pstmt.close();
					}
				} catch (SQLException e) {
					System.out.println("Could not get User for USER_ID "+ userId + e.getMessage());
				} finally {
					dataManager.putConnection(connection);
				}
			}
				
			return username;
		}

		public static boolean updateUser(DataManager dataManager, User user) {
			Connection conn = dataManager.getConnection();
			   PreparedStatement pstmt = null;
			
			try {
			      
				  String sql = "UPDATE MEMBER SET USER_ID=?, FIRST_NAME=?, LAST_NAME=?, USER_NAME=?, BIRTHDAY=?, GENDER=?, E_MAIL=?, STREET=?, ZIPCODE=?, CITY=?, COUNTRY=? WHERE USER_ID=?";
			      	pstmt = conn.prepareStatement(sql); // create a statement
			      	pstmt.setInt(1, user.getUser_id());
			      	pstmt.setString(2, user.getFirstname());// set input parameter 1
		  			pstmt.setString(3, user.getLastname());
		  			pstmt.setString(4, user.getUsername());
		  			pstmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));
		  			pstmt.setString(6, user.getGender());
		  			pstmt.setString(7, user.getEmail());
		  			pstmt.setString(8, user.getStreet());
		  			pstmt.setString(9, user.getZIP());
		  			pstmt.setString(10, user.getCity());
		  			pstmt.setString(11, user.getCountry());
		  			pstmt.setInt(12, user.getUser_id());
		  		
			      
			      pstmt.executeUpdate(); // execute update statement
			    } catch (Exception e) {
			      e.printStackTrace();
			      System.exit(1);
			    } finally {
			      try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return false;
				}
			      try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return false;
				}
			    }
			    return true;
			  }
		

	public static ArrayList getUsernames(DataManager dataManager){
	
	Connection connection  = dataManager.getConnection();
	boolean validator = false;
	User user = new User();
	ArrayList<String> users = new ArrayList<String>();
	
	if (connection != null) {
  		try {
	  		Statement s = connection.createStatement();
		  	String sql="SELECT USER_NAME FROM MEMBER";
        try {
  			  ResultSet rs = s.executeQuery(sql);	
          try {
        	  while(rs.next()){
        		users.add(rs.getString(1)); 
        	  }
        	  
            }
          finally { rs.close(); }
          }
        finally { s.close(); }
		    }
      catch (SQLException e) {
        System.out.println("Could not get all Users as String (UserManager.getUsernames): " + e.getMessage());
        }
      finally {
        dataManager.putConnection(connection);
        }
	}    
  	return users;
  	
	}
	public static boolean addUser(DataManager dataManager, User user) {
		Connection conn = dataManager.getConnection();
		   PreparedStatement pstmt = null;
		
		try {
		      
			  String sql = "INSERT INTO MEMBER VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		      	pstmt = conn.prepareStatement(sql); // create a statement
		      	pstmt.setInt(1, getUserIdSeq(dataManager));
		      	pstmt.setString(2, user.getFirstname());// set input parameter 1
	  			pstmt.setString(3, user.getLastname());
	  			pstmt.setString(4, user.getUsername());
	  			pstmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));
	  			pstmt.setString(6, user.getGender());
	  			pstmt.setString(7, user.getEmail());
	  			pstmt.setString(8, user.getPassword());
	  			pstmt.setString(9, user.getStreet());
	  			pstmt.setString(10, user.getZIP());
	  			pstmt.setString(11, user.getCity());
	  			pstmt.setString(12, user.getCountry());
	  		
		      
		      pstmt.executeUpdate(); // execute update statement
		    } catch (Exception e) {
		      e.printStackTrace();
		      System.exit(1);
		    } finally {
		      try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		      try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("Could not save User UserManager.adduser"+ e.getMessage()); 
				return false;
			}
		    }
		    return true;
	
	}
	public static int getUserIdSeq(DataManager dataManager){
		
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		int seqcount = 0;
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT eBrain.seq_member.nextval FROM dual";
				pstmt = connection.prepareStatement(sql); 
				
				
				try {
					ResultSet rs = pstmt.executeQuery();
					try {
						while (rs.next()) {
							seqcount=rs.getInt(1);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
					pstmt.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get UserManager.getUserIDSequence Seq Int fromseq_member"+ e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
			
		return seqcount;
	}
}

